EMT Practice Test

1. Question Content...

---

Question1: Which of the following isolation levels most reduces concurrency and is most likely to cause lock escalations?

Question2: Given the following statement:
DECLARE GLOBAL TEMPORARY TABLE TEMP1 (
ID INTEGER NOT NULL,
ITEM CHAR(30),
AMOUNT DECIMAL (10,2)
ON COMMIT DELETE ROWS NOT LOGGED;
Which of the following statements are TRUE? (Choose two.)

Question3: BLU Acceleration is included in which of the following edition of DB2 LUW? (Choose two.)

Question4: Which method for restricting data access relies on security labels and security policies to control what data a user can access?

Question5: Which of the following statements regarding locking is TRUE?

Question6: Which of the following statements will successfully create a table that scores index data in a different table space than the table data?

Question7: Which operation typically requires a Share (S) lock?

Question8: Which of the following authorities are sufficient for creating a database? (Choose two.)

Question9: Which of the following is designed for continuous availability, increased throughput of many concurrent short queries, and easy scalability?

Question10: Which locking event occurs when two applications lock rows that are needed by the other, such that neither application can continue executing?

Question11: What is the purpose of a role?

Question12: Which of the following is TRUE about DB2 OLAP functions?

Question13: Which of the following features can provide significant benefits in storage savings, performance of analytic workloads, and time to value?

Question14: If the LOCKSIZE for a table is changed from TABLE to ROW, which of the following is TRUE?

Question15: Which of the following statements describe super exclusive (Z) locks for a table? (Choose two.)

Question16: Consider the following sequence of events:
Eddie grants SELECT WITH GRANT OPTION privilege to Mike.
Mike grants SELECT privilege to Jeff.
Eddie revokes SELECT privilege from Mike.
What will happen to Jeff's SELECT privilege?

Question17: What is the maximum length allowed for a VARCHAR2 data type when the VARCHAR2_COMPAT database configuration parameter is set to ON?

Question18: Which of the following DB2 objects in an efficient way of representing data without the need to maintain it and requires no permanent storage?

Question19: Below are the SQL statements a DBA is planning to execute on a database:
CREATE TABLE t1 (name VARCHAR (1));
INSERT INTO t1 VALUES ('A'), ('B'), ('C'), ('D');
ALTER TABLE t1 ALTER COLUMN name SET DATA TYPE INT;
What will be the outcome of the last statement?

Question20: Which of the following is TRUE regarding user switching in a trusted context?

Question21: Which DB2 object is associated with PREVIOUS VALUE and NEXT VALUE expressions?

Question22: Which of the following commands can undo the work done by the current unit of work executing UPDATE statements on a table?

Question23: What type of function is created using the following command?
CREATE FUNCTION TAN (X DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN SIN(X)/COS(X)

Question24: Which of the following SQL statements will remove all rows from the table T1? (Choose two.)

Question25: The DDL statement is used to create table T1:
CREATE TABLEt1 (
c1 INTEGER,
c2 INTEGER NOT NULL,
c1 DECIMAL(11,2),
c4 TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP
)
Which of the following INSERT statements will execute successfully?

Question26: A portion of your database tables are used for OLTP-type processing and another portion are used for more analytical queries. There is a small subset of tables that are used in both of these usage patterns.
What could you use to derive the analytic improvements offered by BLU technology in such an environment?

Question27: The creator of an index or an index specification automatically receives which privilege on the index?

Question28: The current user wants to produce a list of all user privileges she has been granted. Which table or view can be queried to produce the desired result?

Question29: Consider the following SQL statement:
SELECT workdept, empno, lastname, salary,
RANK() OVER (PARTITION BY workdept ORDER BY salary) AS rank_salary
FROM employee
ORDER BY workdept, lastname
What does the RANK() OVER (PARTITION BY workdept ORDER BY salary) clause do?

Question30: The following statements are executed:
GRANT CREATETAB, DATAACCESS, ON DATABASE TO ROLE role A;
GRANT CREATETAB, BINDADD ON DATABASE TO ROLE role B;
GRANT ROLE role A to USER db2inst3;
GRANT ROLE role B to USER db2inst3;
Which of the following is TRUE if this statement is executed later?
REVOKE ROLE roleB FROM USER db2inst3;

Question31: With HADR, which of the following configuration and topology choices provides the highest level of protection from potential loss of data in a pureScale environment?

Question32: Application 1 executes the following statements:
INSERT INTO TAB1 (NAME,AMOUNT) VALUES ('John,100);
COMMIT;
UPDATE TAB1 SET AMOUNT=90 WHERE NAME='John';
After a few seconds, application 2 executes the following query:
SELECT AMOUNT FROM TAB1 WHERE NAME='John'
Assuming the Currently Committed (CUR_COMMIT) configuration parameter is enabled, which of the following is TRUE?

Question33: What of the following is TRUE about triggers?

Question34: The application owners say they need to be able to query a table and previous versions of that table.
Which type of table is appropriate to satisfy this requirement?

Question35: Which of the following authorities is sufficient for connecting to a database?

Question36: Consider the following query and the resulting set of data:
SELECT empno, lastname FROM emp WHERE empno BETWEEN '000020' and '000070' Result:

If the query below is executed, how many rows will be returned?
SELECT empno FROM emp WHERE empno > '000020' AND empno '000070'